SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[paObtenerFormularioDelTramite]

@IdTramite INT,
@IdTrackingDePasos INT = NULL,
@Formulario INT OUTPUT

AS
SET NOCOUNT ON
IF @IdTrackingDePasos IS NULL BEGIN
	SELECT TOP 1 @Formulario = Formulario FROM tb_Tracking TT
		INNER JOIN tb_Pasos PP ON PP.Tarea = TT.Tarea
		WHERE PP.TipoDePaso = 1 AND TT.Estado = 4 AND TT.Tramite = @IdTramite AND
		NOT EXISTS(SELECT * FROM tb_TrackingDePasos TP WHERE TP.Tracking = TT.IdTracking AND PP.IdPaso = TP.Paso)
		ORDER BY PP.NroOrdenEnTarea
END
ELSE BEGIN
	SELECT @Formulario = Formulario FROM tb_Tracking TT 
		INNER JOIN tb_Pasos PP ON PP.Tarea = TT.Tarea
		INNER JOIN tb_TrackingDePasos TP ON TP.Tracking = TT.IdTracking AND PP.IdPaso = TP.Paso
		WHERE TT.Tramite = @IdTramite AND TP.IdTrackingPasos = @IdTrackingDePasos
END
GO
GRANT EXECUTE ON  [dbo].[paObtenerFormularioDelTramite] TO [GestionDeDocumentos]
GRANT EXECUTE ON  [dbo].[paObtenerFormularioDelTramite] TO [SoloVer]
GO
